﻿ALTER TABLE [hms909].[dbo].[Event_OperationsMA] drop     CONSTRAINT [op-event] ;
ALTER TABLE [hms909].[dbo].[Event_OperationsMA] drop     CONSTRAINT [op-patID];
ALTER TABLE [hms909].[dbo].[Event_OperationsMA] drop     CONSTRAINT [op-doc] ; 
ALTER TABLE [hms909].[dbo].[Event_OperationsMA] drop     CONSTRAINT [op-opID] ; 
ALTER TABLE [hms909].[dbo].[Event_OperationsMA] drop     CONSTRAINT [op-roomID];
ALTER TABLE [hms909].[dbo].[DoctorsMA] drop  CONSTRAINT [doctors_specialty2] 
ALTER TABLE [hms909].[dbo].[DoctorsMA] drop  CONSTRAINT [doctors_rank2] 
  ALTER table [hms909].[dbo].[Events] drop  CONSTRAINT [event-docid]
  ALTER table [hms909].[dbo].[Events] drop  CONSTRAINT [event-roomID] 
  ALTER table [hms909].[dbo].[Events] drop  CONSTRAINT [event-patID] 

     alter table [hms909].[dbo].[Event_OperationsMA] drop  CONSTRAINT [op-event]
   alter table [hms909].[dbo].[Event_OperationsMA] drop CONSTRAINT [op-patID] 
    alter table [hms909].[dbo].[Event_OperationsMA] drop CONSTRAINT [op-doc] 
   alter table [hms909].[dbo].[Event_OperationsMA] drop CONSTRAINT [op-opID] 
   alter table [hms909].[dbo].[Event_OperationsMA] drop CONSTRAINT [op-roomID] 

DROP TABLE  [hms909].[dbo].[Event_OperationsMA];
DROP TABLE [hms909].[dbo].[Events];  
DROP TABLE  [hms909].[dbo].[DoctorsMA];

DROP TABLE  [hms909].[dbo].[OperationsMA];
DROP TABLE [hms909].[dbo].[SpecialtyMA];

DROP TABLE [hms909].[dbo].[RanksMA];

DROP TABLE [hms909].[dbo].[RoomsMA];

CREATE TABLE [hms909].[dbo].[RoomsMA]
(
	[ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY , 
    [English] NCHAR(100) NOT NULL, 
    [Arabic] NCHAR(100) NULL
)

CREATE TABLE [hms909].[dbo].[SpecialtyMA]
(
	[ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    [english] NCHAR(100) NOT NULL, 
    [arabic] NCHAR(100) NOT NULL
)
CREATE TABLE [hms909].[dbo].[RanksMA]
(
	[ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    [Arabic] NCHAR(100) NOT NULL, 
    [English] NCHAR(100) NOT NULL
)
CREATE TABLE [hms909].[dbo].[DoctorsMA]
(
	[ID] INT NOT NULL  IDENTITY(1,1) PRIMARY KEY, 
    [English] NCHAR(100) NOT NULL, 
    [Arabic] NCHAR(100) NOT NULL, 
    [Speciality] INT NOT NULL, 
    [Color] NCHAR(100) NULL, 
    [Rank] INT NOT NULL, 
    CONSTRAINT [doctors_specialty2] FOREIGN KEY ([Speciality]) REFERENCES [SpecialtyMA]([ID]), 
    CONSTRAINT [doctors_rank2] FOREIGN KEY ([Rank]) REFERENCES [RanksMA]([ID])
)
CREATE TABLE [hms909].[dbo].[OperationsMA]
(
	[ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    [English] NCHAR(100) NOT NULL, 
    [Arabic] NCHAR(100) NOT NULL, 
    [Speciality] INT NOT NULL, 
    [Duration] INT NULL, 
    CONSTRAINT [operation_speciaty] FOREIGN KEY ([Speciality]) REFERENCES [SpecialtyMA]([ID])
)
CREATE TABLE [hms909].[dbo].[Events](
  [id] int IDENTITY(1,1) NOT NULL,
  [text] nvarchar(2056) NULL,
  [start_date] datetime NOT NULL,
  [end_date] datetime NOT NULL,
  [docID] INT NOT NULL, 
    [roomID] INT NOT NULL, 
    [patID] VARCHAR(6) NOT NULL, 
    PRIMARY KEY (id), 
    CONSTRAINT [event-docid] FOREIGN KEY ([docID]) REFERENCES [DoctorsMA]([ID]), 
    CONSTRAINT [event-roomID] FOREIGN KEY ([roomID]) REFERENCES [RoomsMA]([ID]), 
    CONSTRAINT [event-patID] FOREIGN KEY ([patID]) REFERENCES [Cust]([ID])
)

CREATE TABLE [hms909].[dbo].[Event_OperationsMA]
(
	[ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    [eventID] INT NOT NULL, 
    [patID] VARCHAR(6) NOT NULL, 
    [docID] INT NOT NULL, 
    [opID] INT NOT NULL, 
    [roomID] INT NOT NULL, 
    CONSTRAINT [op-event] FOREIGN KEY ([eventID]) REFERENCES [Events]([id]), 
    CONSTRAINT [op-patID] FOREIGN KEY ([patID]) REFERENCES [Cust]([id]), 
    CONSTRAINT [op-doc] FOREIGN KEY ([docID]) REFERENCES [DoctorsMA]([id]), 
    CONSTRAINT [op-opID] FOREIGN KEY ([opID]) REFERENCES [OperationsMA]([id]), 
    CONSTRAINT [op-roomID] FOREIGN KEY ([roomID]) REFERENCES [RoomsMA]([id])
)
